/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import OBJECT.TuyenDuong;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author TAN
 */
public class TuyenDuongDAO {
     private Connect connect=null;

    
        
    public TuyenDuongDAO(String User ,String Pass,String port,String DataBase,String Host) {
        connect =new Connect(User , Pass, port, DataBase, Host);

    }
    
    
    public int SuaTuyenDuong(int maTuyenDuong, String tenTuyenDuong, String gaBatDau, 
            String gaCuoiCung, float tongKM, String tinhTrang) {
        Connection con = connect.GetConnect();
        CallableStatement cal; 
        try {
            cal = con.prepareCall("{call sp_CapNhatTuyenDuong(?,?,?,?,?,?,?)}"); //con.prepareCall("{call sp_ThemTau(?,?,?)}");
            cal.setInt(1, maTuyenDuong);
            cal.setString(2, tenTuyenDuong);
            cal.setString(3, gaBatDau);
            cal.setString(4, gaCuoiCung);
            cal.setFloat(5, tongKM);
            cal.setString(6, tinhTrang);
            cal.registerOutParameter(7, Types.INTEGER);
            cal.setInt(7, 1);
            cal.execute();
            int flag = cal.getInt(7);
            if(flag == 1) {
                return -1;
            } else {
                return 1;
            }
        } catch (SQLException ex) {
            Logger.getLogger(TauDAO1.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }
    
    public int ThemTuyenDuong(String tenTuyenDuong, String gaBatDau, 
            String gaCuoiCung, float tongKM, String tinhTrang) {
        Connection con = connect.GetConnect();
        CallableStatement cal; 
        try {
            cal = con.prepareCall("{call sp_ThemTuyenDuong(?,?,?,?,?,?)}"); //con.prepareCall("{call sp_ThemTau(?,?,?)}");
            cal.setString(1, tenTuyenDuong);
            cal.setString(2, gaBatDau);
            cal.setString(3, gaCuoiCung);
            cal.setFloat(4, tongKM);
            cal.setString(5, tinhTrang);
            cal.registerOutParameter(6, Types.INTEGER);
            cal.setInt(6, 1);
            cal.execute();
            int flag = cal.getInt(6);
            if(flag == 1) {
                return -1;
            } else {
                return 1;
            }
        } catch (SQLException ex) {
            Logger.getLogger(TauDAO1.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }
    
    public int XoaTuyenDuong(int maTuyenDuong) {
        //Connection con  = util.Connection.getConnection();
        Connection con = connect.GetConnect();
        CallableStatement cal; 
        try {
            cal = con.prepareCall("{call sp_XoaTuyenDuong(?,?)}"); //con.prepareCall("{call sp_ThemTau(?,?,?)}");
            cal.setInt(1, maTuyenDuong);
            cal.registerOutParameter(2, Types.INTEGER);
            cal.setInt(2, 1);
            cal.execute();
            int flag = cal.getInt(2);
            if(flag == 1) {
                return -1;
            } else {
                return 1;
            }
         } catch (SQLException ex) {
            Logger.getLogger(TauDAO1.class.getName()).log(Level.SEVERE, null, ex);
         }
        //System.out.print(tenTau);
        return 0;
    }
    
    public List<TuyenDuong> LayDanhSachTuyenDuong(){
        List<TuyenDuong> ds = new ArrayList<TuyenDuong>();
        Connection conn = null;
        CallableStatement cals = null;
        ResultSet rs = null;
        try {
//            conn = util.Connection.getConnection();
            conn = connect.GetConnect();
            cals = conn.prepareCall("{call sp_ThongTinTuyenDuong()}");
            rs = cals.executeQuery();
            TuyenDuong td = null;
            while(rs.next()){
                td = new TuyenDuong();
                td.setMaTuyenDuong(rs.getInt("Ma_TuyenDuong"));
                td.setTenTuyenDuong(rs.getString("Ten_TuyenDuong"));
                td.setGaXuatPhat(rs.getString("GaXuatPhat"));
                td.setGaCuoiCung(rs.getString("GaCuoiCung"));
                td.setTongKM(rs.getFloat("TongKM"));
                td.setTinhTrang(rs.getString("TinhTrangTD"));
                ds.add(td);
            }
        } catch (Exception e) {
            System.err.println(e.getMessage());
        }finally{
            try {
                if(rs != null){
                    rs.close();
                }
                cals.close();
                CloseConnect();
            } catch (Exception e) {
            }
        }
        
        return ds;
    }
    
      public CallableStatement DanhSachTuyenDuong()
    {
        Connection conn=connect.GetConnect();
        ResultSet result=null;
        CallableStatement cals=null;
        try {
             cals=conn.prepareCall("{call sp_ThongTinTuyenDuong()}");
              return cals;    
              
        } catch (SQLException ex) {
          ;
            
        }
       return cals;
    }
       public void CloseConnect()
      {
          try
          {
          connect.Close();
          }catch(Exception e)
          {
              ;
          }
      } 

}
